Este trabalho trata da Avaliação 2 da disciplina de Banco de Dados do curso Ciêncisa de Dados/FGV-EMAp objetivando aperfeiçoar e colocar em prática os aprendizados do semestre. Neste caso, a proposta é capturar dados GeoJson, armanezar no banco MySql e, posteriormente, analisar as informações.
Para tanto, capturamos os dados de Arquivos GeoJSON do Estado de São Paulo e datasets com informações adicionais sobre os municípios, como o Índice de Nível Socioeconômico, o Índice de Desenvolvimento da Educação do Estado de São Paulo, fluxo escolar, a quantidade de população por município e referências sobre a segurança, extraídos do site Base dos Dados, afim de entedermos como essas estão distribuídas no espaço geográfico do Estado, averiguando quais municípios se destacam e como os dados se correlacionam.
# Bibliotecas para ler os dados dos arquivos e para manipulação (transformação) de dados
import os
import sys
import time
import datetime
import numpy as np
import pandas as pd
import pathlib
import geopandas as gpd
from sklearn.preprocessing import MaxAbsScaler
abs_scaler = MaxAbsScaler()
# Bibliotecas para visualização
import geoplot.crs as gcrs
import geoplot as gplt
import matplotlib.pyplot as plt
import matplotlib as ptl
import seaborn as sns
import plotly.express as px
from plotly.subplots import make_subplots
# Para imprimir os gráficos inline
%matplotlib inline
# Bibliotecas para conectar com o MySQL
import getpass
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
import pandas.io.sql as psql
# Para salvar os gráficos interativos
import plotly.io as pio
pio.renderers.default = "notebook"
Para esse trabalho pretendemos utilizar 5 diferentes tabelas, uma para os dados geométricos, do Geodata BR, e outras quatro com dados a respeito da educação, segurança e população do estado de São Paulo, da Base dos dados. Dessa forma, vamos primeiramente importar os dados dos arquivos GeoJSON e CSV para o pandas e o geopandas para limpá-los e posteriormente colocá-los no MySQL utilizando o pymysql e o sqlalchemy.
Começaremos pelos dados de geometria:
# Lendo o arquivo GeoJSON e importando os dados para um geodataframe
geometria = gpd.read_file('.//bases_de_dados//geojs-35-mun.json')
# Alterando o indice da tabela (para não ficar duplicado)
geometria.set_index("id", drop=True, inplace=True)
# Imprimindo os tipos de dados (para conferir se foi reconhecida a coluna de geometria)
print(geometria.dtypes)
# Imprimindo os primeiros valores da tabela
geometria.head(3)
name object description object geometry geometry dtype: object
| name | description | geometry | |
|---|---|---|---|
| id | |||
| 3500105 | Adamantina | Adamantina | POLYGON ((-51.05787 -21.39888, -51.05365 -21.4... |
| 3500204 | Adolfo | Adolfo | POLYGON ((-49.65478 -21.20607, -49.63847 -21.2... |
| 3500303 | Aguaí | Aguaí | POLYGON ((-47.20890 -21.97129, -47.20297 -21.9... |
A primeira vista, as colunas name e description parecem ser iguais. Caso sejam, desejados armazenar apenas uma delas. Conferindo se as colunas nome e descrição são iguais:
contador = 0
# Verifica se os elementos das colunas são iguais para cada linha
for row in geometria.iterrows():
if row[1]["name"] != row[1]["description"]:
print("Existe nome diferente da descrição")
contador += 1
# Se não foi encontrada nenhuma linha
if contador == 0:
print("As duas colunas são iguais")
As duas colunas são iguais
Como vimos acima, as duas colunas são iguais, então vamos remover a coluna description:
# Removendo a coluna
geometria.drop('description', axis='columns', inplace=True)
#Exibindo o resultado
geometria.head(3)
| name | geometry | |
|---|---|---|
| id | ||
| 3500105 | Adamantina | POLYGON ((-51.05787 -21.39888, -51.05365 -21.4... |
| 3500204 | Adolfo | POLYGON ((-49.65478 -21.20607, -49.63847 -21.2... |
| 3500303 | Aguaí | POLYGON ((-47.20890 -21.97129, -47.20297 -21.9... |
Vamos verificar o nome de maior tamanho na coluna name para posteriormente criar a tabela no MySQL (esse processo também foi feito para as outras colunas, mas não exibiremos esse processo para todas as colunas para não ser excessivamente repetitivo)
geometria["name"].apply(lambda x: len(x)).max()
26
Com isso, vamos fazer de forma análoga para as próximas tabelas
Nesse caso, diferentemente de como foi feito anteriormente, também precisamos remover valores faltantes na coluna com o id do município (código do IBGE - o id da tabela anterior). Abaixo vamos ler o arquivo CSV e remover os valores faltantes:
# Lendo o arquivo CSV e importando os dados para um dataframe
idesp = pd.read_csv('.//bases_de_dados//escola_IDESP.csv')
# Removendo os valores faltantes em "id_municipio"
idesp = idesp[~np.isnan(idesp["id_municipio"])]
# Transformando os valores de "id_municipio" em inteiros
idesp["id_municipio"] = idesp["id_municipio"].apply(lambda x: int(x))
# Imprimendo o resultado
idesp.head(3)
| ano | id_municipio | id_escola | id_escola_sp | nota_idesp_ef_iniciais | nota_idesp_ef_finais | nota_idesp_em | |
|---|---|---|---|---|---|---|---|
| 0 | 2007 | 3550308 | 35000024 | 24 | 2.88 | 2.52 | 1.32 |
| 1 | 2007 | 3550308 | 35000048 | 48 | NaN | 2.26 | 0.93 |
| 2 | 2007 | 3550308 | 35000061 | 61 | 3.02 | NaN | NaN |
De forma análoga as tabelas anteriores:
# Lendo o arquivo CSV e importando os dados para um dataframe
escolas_nse = pd.read_csv('.//bases_de_dados//escola_nse.csv')
# Exibindo o resultado
escolas_nse.head(3)
| id_municipio | rede | diretoria | id_escola | id_escola_sp | nivel_socio_economico | |
|---|---|---|---|---|---|---|
| 0 | 3550308 | estadual | NORTE 1 | 35000012 | 12 | 2.71 |
| 1 | 3550308 | estadual | NORTE 1 | 35000024 | 24 | 3.93 |
| 2 | 3550308 | estadual | NORTE 1 | 35000036 | 36 | 4.02 |
Nesse caso, além do que já foi feito anteriormente, também precisamos remover valores inconsistentes pois há campos na tabela com proporções maiores que 100%. Fazemos isso abaixo:
# Lendo o arquivo CSV e importando os dados para um dataframe
fluxo_escolar = pd.read_csv('.//bases_de_dados//fluxo_escolar.csv')
# Removendo os valores faltantes em "id_municipio"
fluxo_escolar = fluxo_escolar[~np.isnan(fluxo_escolar["id_municipio"])]
# Transformando os valores de "id_municipio" em inteiros
fluxo_escolar["id_municipio"] = fluxo_escolar["id_municipio"].apply(lambda x: int(x))
# Removendo os dados inconsistentes (proporção maior que 100%)
fluxo_escolar = fluxo_escolar[~(fluxo_escolar["prop_aprovados_em"] > 100)]
fluxo_escolar = fluxo_escolar[~(fluxo_escolar["prop_reprovados_em"] > 100)]
fluxo_escolar = fluxo_escolar[~(fluxo_escolar["prop_abandono_em"] > 100)]
# Exibindo o resultado
fluxo_escolar.head(3)
| ano | sigla_uf | rede | diretoria | id_municipio | id_escola | id_escola_sp | codigo_tipo_escola | prop_aprovados_anos_inciais_ef | prop_reprovados_anos_iniciais_ef | prop_abandono_anos_iniciais_ef | prop_aprovados_anos_finais_ef | prop_reprovados_anos_finais_ef | prop_abandono_anos_finais_ef | prop_aprovados_em | prop_reprovados_em | prop_abandono_em | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2011 | SP | estadual | NORTE 1 | 3550308 | 35910995 | 910995 | NaN | 0.000000 | 0.000000 | 0.000000 | 0.0 | 0.0 | 0.0 | 65.032257 | 32.774193 | 2.193548 |
| 1 | 2011 | SP | estadual | NORTE 1 | 3550308 | 35910296 | 910296 | NaN | 98.034935 | 0.655022 | 1.310044 | 0.0 | 0.0 | 0.0 | 0.000000 | 0.000000 | 0.000000 |
| 2 | 2011 | SP | estadual | NORTE 1 | 3550308 | 35923618 | 923618 | NaN | 97.419357 | 0.967742 | 1.612903 | 0.0 | 0.0 | 0.0 | 0.000000 | 0.000000 | 0.000000 |
De forma análoga as tabelas anteriores:
# Lendo o arquivo CSV e importando os dados para um dataframe
ocorrencias_sp = pd.read_csv('.//bases_de_dados//ocorrencias_registradas.csv')
# Exibindo o resultado
ocorrencias_sp.head(3)
| ano | mes | id_municipio | regiao_ssp | homicidio_doloso | numero_de_vitimas_em_homicidio_doloso | homicidio_doloso_por_acidente_de_transito | numero_de_vitimas_em_homicidio_doloso_por_acidente_de_transito | homicidio_culposo_por_acidente_de_transito | homicidio_culposo_outros | ... | total_de_estupro | estupro | estupro_de_vulneravel | total_de_roubo_outros | roubo_outros | roubo_de_veiculo | roubo_a_banco | roubo_de_carga | furto_outros | furto_de_veiculo | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2002 | 1 | 3500105 | Presidente Prudente | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | NaN | NaN | 0.0 | NaN | 0.0 | 0.0 | 0.0 | 21.0 | 0.0 |
| 1 | 2002 | 2 | 3500105 | Presidente Prudente | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | NaN | NaN | 0.0 | NaN | 0.0 | 0.0 | 0.0 | 32.0 | 0.0 |
| 2 | 2002 | 3 | 3500105 | Presidente Prudente | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | NaN | NaN | 0.0 | NaN | 0.0 | 0.0 | 0.0 | 36.0 | 0.0 |
3 rows × 27 columns
Além do que já foi feito anteriormente, nesse caso desejamos manter apenas os dados referentes ao estado de São Paulo, já que nas tabelas anteriores todos os dados já eram de São Paulo, porém nessa os dados são do Brasil inteiro. Fazemos isso abaixo:
# Lendo o arquivo CSV e importando os dados
populacao_brasil = pd.read_csv('.//bases_de_dados//municipio.csv')
# Mantendo somente os dados de cidades do estado de São Paulo
populacao_sp = populacao_brasil[populacao_brasil["id_municipio"].isin(geometria.index.astype(int))].copy()
# Exibindo o resultado
populacao_sp.head(3)
| ano | sigla_uf | id_municipio | populacao | |
|---|---|---|---|---|
| 98994 | 1991 | SP | 3500105 | 32092.0 |
| 98995 | 1992 | SP | 3500105 | 32085.0 |
| 98996 | 1993 | SP | 3500105 | 32099.0 |
Agora que já temos os dados limpos adequadamente, desejamos criar um banco de dados no MySQL para armazenar todos os dados que temos. Para isso, primeiramente criamos um modelo físico no Vertabelo para nos auxiliar:
Vale lembar que os tipos de dados foram definidos de acordo como já vimos anteriormente quando limpamos eles, assim como verificamos também os casos que temos valores faltantes (sendo que muitos deles desejamos manter assim, sem remover essas observações)
Devemos criar uma conexão com o MySQL para conseguir criar o banco de dados e as tabelas utilizando python. Para isso, nesta etapa, utilizaremos o pymysql:
# Recebendo a senha do usuário (foi utilizado o getpass para cada usuário inserir sua senha e ela não ser exibida)
p = getpass.getpass()
# Criando a conexão com o MySQL
connection = pymysql.connect(host='localhost', port=3306, user='root', passwd=p) #, db='mysql')
# Criando um cursor
cursor = connection.cursor()
# Ativando o autocommit (para não ser necessário realizar um commit a cada alteração)
connection.autocommit(True)
········
Como estamos trabalhando coletivamente e esse notebook deve funcionar para rodar as "células" em sequência e funcionar todas as etapas, devemos remover (apagar, deletar) a base de dados caso exista no computador do respectivo usuário e criar ela novamente:
# Apagando a base de dados caso exista
cursor.execute("DROP DATABASE IF EXISTS educacao_sao_paulo;")
# Criando a base de dados
cursor.execute("create database educacao_sao_paulo;")
1
Agora que criamos a base de dados, devemos usá-la para poder prosseguir com a etapa de criar tabelas. Utilizamos o nosso cursor novamente e passamos a utilizar a base de dados criada com o comando do MySQL:
# Definindo o uso da nossa base de dados criada anteriormente
cursor.execute("use educacao_sao_paulo;")
0
Utilizando o modelo que foi criado no Vertabelo, agora vamos criar nossas bases de dados no MySQL. Abaixo, criaremos as respectivas tabelas para cada subtópico.
cursor.execute("""
CREATE TABLE geometria (
id int NOT NULL,
name varchar(40) NOT NULL,
geometry polygon NOT NULL,
CONSTRAINT geometria_pk PRIMARY KEY (id)
);""")
0
cursor.execute("""
CREATE TABLE escola_nse (
id int NOT NULL AUTO_INCREMENT,
id_municipio int NOT NULL,
rede varchar(40) NOT NULL,
diretoria varchar(40) NOT NULL,
id_escola_sp int NOT NULL,
id_escola int NOT NULL,
nivel_socio_economico float(6,3) NULL,
CONSTRAINT escola_nse_pk PRIMARY KEY (id)
);
""")
0
cursor.execute("""
CREATE TABLE fluxo_escolar (
id int NOT NULL AUTO_INCREMENT,
id_municipio int NOT NULL,
ano int NOT NULL,
sigla_uf varchar(2) NOT NULL,
rede varchar(40) NULL,
diretoria varchar(40) NOT NULL,
id_escola int NOT NULL,
id_escola_sp int NOT NULL,
codigo_tipo_escola int NULL,
prop_aprovados_anos_inciais_ef float(11,8) NULL,
prop_reprovados_anos_iniciais_ef float(11,8) NULL,
prop_abandono_anos_iniciais_ef float(11,8) NULL,
prop_aprovados_anos_finais_ef float(11,8) NULL,
prop_reprovados_anos_finais_ef float(11,8) NULL,
prop_abandono_anos_finais_ef float(11,8) NULL,
prop_aprovados_em float(11,8) NULL,
prop_reprovados_em float(11,8) NULL,
prop_abandono_em float(11,8) NULL,
CONSTRAINT fluxo_escolar_pk PRIMARY KEY (id)
);
""")
0
cursor.execute("""
CREATE TABLE idesp (
id int NOT NULL AUTO_INCREMENT,
id_municipio int NOT NULL,
ano int NOT NULL,
id_escola int NOT NULL,
id_escola_sp int NOT NULL,
nota_idesp_ef_iniciais float(6,3) NULL,
nota_idesp_ef_finais float(6,3) NULL,
nota_idesp_em float(6,3) NULL,
CONSTRAINT idesp_pk PRIMARY KEY (id)
);
""")
0
cursor.execute("""
CREATE TABLE ocorrencias_sp (
id int NOT NULL AUTO_INCREMENT,
id_municipio int NOT NULL,
ano int NOT NULL,
mes int NOT NULL,
regiao_ssp varchar(40) NOT NULL,
homicidio_doloso int NULL,
numero_de_vitimas_em_homicidio_doloso int NULL,
homicidio_doloso_por_acidente_de_transito int NULL,
numero_de_vitimas_em_homicidio_doloso_por_acidente_de_transito int NULL,
homicidio_culposo_por_acidente_de_transito int NULL,
homicidio_culposo_outros int NULL,
tentativa_de_homicidio int NULL,
lesao_corporal_seguida_de_morte int NULL,
lesao_corporal_dolosa int NULL,
lesao_corporal_culposa_por_acidente_de_transito int NULL,
lesao_corporal_culposa_outras int NULL,
latrocinio int NULL,
numero_de_vitimas_em_latrocinio int NULL,
total_de_estupro int NULL,
estupro int NULL,
estupro_de_vulneravel int NULL,
total_de_roubo_outros int NULL,
roubo_outros int NULL,
roubo_de_veiculo int NULL,
roubo_a_banco int NULL,
roubo_de_carga int NULL,
furto_outros int NULL,
furto_de_veiculo int NULL,
CONSTRAINT ocorrencias_sp_pk PRIMARY KEY (id)
);
""")
0
cursor.execute("""
CREATE TABLE populacao_sp (
id int NOT NULL AUTO_INCREMENT,
ano int NOT NULL,
sigla_uf varchar(2) NOT NULL,
id_municipio int NOT NULL,
populacao int NULL,
CONSTRAINT populacao_sp_pk PRIMARY KEY (id)
);
""")
0
Agora que já criamos as tabelas corretamente no MySQL, desejamos popular nossa base de dados. Para isso, utilizaremos o pymysql e o sqlalchemy, já que temos uma tabela com dados de geometria e as demais não possuem.
Populamos a tabela geometria utilizando o pymysql (pois o geopandas oferece métodos para popular tabelas no ProgreSQL, mas não no MySQL):
# Iteremos em cada linha da tabela de geometria para inserí-las uma a uma no MySQL
for row in geometria.iterrows():
# Criamos uma tupla com os dados necessários
data = (row[0], row[1]["name"], row[1]["geometry"].wkt)
# Criamos uma string com o comando para popular a tabela no MySQL (com valores a serem preenchidos pelos dados da respectiva linha)
sql = """INSERT INTO geometria (id, name, geometry) VALUES (%s, %s, ST_GeomFromText(%s, 0))"""
# Inserimos os dados linha a linha
cursor.execute(sql,data)
Vale lembrar que acima convertemos os dados de geometria ao inserir no MySQL para que o tipo de dado seja aceito como polygon no MySQL.
Para as próximas tabelas utilizaremos o sqlalchemy, então devemos criar uma conexão por ele:
# Criando a conexão (utilizando a senha já inserida anteriormente)
conectar = f'mysql+pymysql://root:{p}@localhost/educacao_sao_paulo'
engine = create_engine(conectar)
Agora, podemos popular a tabela dos dados do IDESP, utilizando o método to_sql() que o pandas oferece:
idesp.to_sql('idesp', con=engine, index=False, if_exists="append")
Faremos de forma análoga para as próximas tabelas, nos próximos subtópicos abaixo
escolas_nse.to_sql('escola_nse', con=engine, index=False, if_exists="append")
fluxo_escolar.to_sql('fluxo_escolar', con=engine, index=False, if_exists="append")
ocorrencias_sp.to_sql('ocorrencias_sp', con=engine, index=False, if_exists="append")
populacao_sp.to_sql('populacao_sp', con=engine, index=False, if_exists="append")
Agora que já populamos as tabelas, podemos adicionar as foreign keys, conforme nosso modelo físico criado no Vertabelo. Isso não foi feito anteriormente para não haver problemas ao popular a base de dados, pois a ordem para inserior os dados seria mais importante, sendo assim adicionar as foreign keys após popular as tabelas é mais conveniente. Adicionando as foreign keys:
# Adicionando a foreign key a tabela "escola_nse"
cursor.execute("""
ALTER TABLE escola_nse ADD CONSTRAINT escola_nse_geometria FOREIGN KEY escola_nse_geometria (id_municipio)
REFERENCES geometria (id);
""")
# Adicionando a foreign key a tabela "fluxo_escolar"
cursor.execute("""
ALTER TABLE fluxo_escolar ADD CONSTRAINT fluxo_escolar_geometria FOREIGN KEY fluxo_escolar_geometria (id_municipio)
REFERENCES geometria (id);
""")
# Adicionando a foreign key a tabela "idesp"
cursor.execute("""
ALTER TABLE idesp ADD CONSTRAINT idesp_geometria FOREIGN KEY idesp_geometria (id_municipio)
REFERENCES geometria (id);
""")
# Adicionando a foreign key a tabela "ocorrencias_sp"
cursor.execute("""
ALTER TABLE ocorrencias_sp ADD CONSTRAINT ocorrencias_sp_geometria FOREIGN KEY ocorrencias_sp_geometria (id_municipio)
REFERENCES geometria (id);
""")
# Adicionando a foreign key a tabela "populacao_sp"
cursor.execute("""
ALTER TABLE populacao_sp ADD CONSTRAINT populacao_sp_geometria FOREIGN KEY populacao_sp_geometria (id_municipio)
REFERENCES geometria (id);
""")
19842
Agora que conseguimos popular o banco de dados no MySQL corretamente, desejamos importá-los novamente para o pandas e o geopandas para que possamos fazer as análises com os dados. Utilizaremos nomes de variáveis diferentes para reforçar que estamos utilizando os dados que foram populados no MySQL.
Abaixo importaremos os dados dos repectivos subtópicos.
# Importando os dados desejados para o pandas e convertendo a geometria do MySQL novamente para ser possível ler no geopandas
mapassp = psql.read_sql('SELECT id, name, ST_AsWKT(geometry) as geometry FROM geometria;', con=connection)
# Convertendo o tipo de dado da geometria com o geopandas para ser reconhecido como geometria
mapassp['geometry'] = gpd.GeoSeries.from_wkt(mapassp['geometry'])
# Convertendo o DataFrame para um GeoDataFrame
mapassp = gpd.GeoDataFrame(mapassp, geometry="geometry")
# Imprimindo os tipos de dados
print(mapassp.dtypes)
# Imprimendo o resultado da importação
mapassp.head(3)
id int64 name object geometry geometry dtype: object
| id | name | geometry | |
|---|---|---|---|
| 0 | 3500105 | Adamantina | POLYGON ((-51.05787 -21.39888, -51.05365 -21.4... |
| 1 | 3500204 | Adolfo | POLYGON ((-49.65478 -21.20607, -49.63847 -21.2... |
| 2 | 3500303 | Aguaí | POLYGON ((-47.20890 -21.97129, -47.20297 -21.9... |
Como podemos ver acima, conseguimos importar nossos dados novamente e fazê-los serem reconhecidos como geometria pelo geopandas.
# Importandos os dados para um DataFrame do pandas
sp_nse = psql.read_sql('SELECT * FROM escola_nse;', con=connection)
# Exibindo o resultado da importação
sp_nse.head(3)
| id | id_municipio | rede | diretoria | id_escola_sp | id_escola | nivel_socio_economico | |
|---|---|---|---|---|---|---|---|
| 0 | 1 | 3550308 | estadual | NORTE 1 | 12 | 35000012 | 2.71 |
| 1 | 2 | 3550308 | estadual | NORTE 1 | 24 | 35000024 | 3.93 |
| 2 | 3 | 3550308 | estadual | NORTE 1 | 36 | 35000036 | 4.02 |
# Importandos os dados para um DataFrame do pandas
sp_idesp = psql.read_sql('SELECT * FROM idesp;', con=connection)
# Exibindo o resultado da importação
sp_idesp.head(3)
| id | id_municipio | ano | id_escola | id_escola_sp | nota_idesp_ef_iniciais | nota_idesp_ef_finais | nota_idesp_em | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 3550308 | 2007 | 35000024 | 24 | 2.88 | 2.52 | 1.32 |
| 1 | 2 | 3550308 | 2007 | 35000048 | 48 | NaN | 2.26 | 0.93 |
| 2 | 3 | 3550308 | 2007 | 35000061 | 61 | 3.02 | NaN | NaN |
# Importandos os dados para um DataFrame do pandas
sp_fluxo_escolar = psql.read_sql('SELECT * FROM fluxo_escolar;', con=connection)
# Exibindo o resultado da importação
sp_fluxo_escolar.head(3)
| id | id_municipio | ano | sigla_uf | rede | diretoria | id_escola | id_escola_sp | codigo_tipo_escola | prop_aprovados_anos_inciais_ef | prop_reprovados_anos_iniciais_ef | prop_abandono_anos_iniciais_ef | prop_aprovados_anos_finais_ef | prop_reprovados_anos_finais_ef | prop_abandono_anos_finais_ef | prop_aprovados_em | prop_reprovados_em | prop_abandono_em | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 3550308 | 2011 | SP | estadual | NORTE 1 | 35910995 | 910995 | NaN | 0.000000 | 0.000000 | 0.000000 | 0.0 | 0.0 | 0.0 | 65.032257 | 32.774193 | 2.193548 |
| 1 | 2 | 3550308 | 2011 | SP | estadual | NORTE 1 | 35910296 | 910296 | NaN | 98.034935 | 0.655022 | 1.310044 | 0.0 | 0.0 | 0.0 | 0.000000 | 0.000000 | 0.000000 |
| 2 | 3 | 3550308 | 2011 | SP | estadual | NORTE 1 | 35923618 | 923618 | NaN | 97.419357 | 0.967742 | 1.612903 | 0.0 | 0.0 | 0.0 | 0.000000 | 0.000000 | 0.000000 |
# Importandos os dados para um DataFrame do pandas
df_seguranca = psql.read_sql('SELECT * FROM ocorrencias_sp;', con=connection)
# Exibindo o resultado da importação
df_seguranca.head(3)
| id | id_municipio | ano | mes | regiao_ssp | homicidio_doloso | numero_de_vitimas_em_homicidio_doloso | homicidio_doloso_por_acidente_de_transito | numero_de_vitimas_em_homicidio_doloso_por_acidente_de_transito | homicidio_culposo_por_acidente_de_transito | ... | total_de_estupro | estupro | estupro_de_vulneravel | total_de_roubo_outros | roubo_outros | roubo_de_veiculo | roubo_a_banco | roubo_de_carga | furto_outros | furto_de_veiculo | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 3500105 | 2002 | 1 | Presidente Prudente | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | NaN | NaN | 0.0 | NaN | 0.0 | 0.0 | 0.0 | 21.0 | 0.0 |
| 1 | 2 | 3500105 | 2002 | 2 | Presidente Prudente | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | NaN | NaN | 0.0 | NaN | 0.0 | 0.0 | 0.0 | 32.0 | 0.0 |
| 2 | 3 | 3500105 | 2002 | 3 | Presidente Prudente | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | NaN | NaN | 0.0 | NaN | 0.0 | 0.0 | 0.0 | 36.0 | 0.0 |
3 rows × 28 columns
# Importandos os dados para um DataFrame do pandas
sp_populacao = psql.read_sql('SELECT * FROM populacao_sp;', con=connection)
# Exibindo o resultado da importação
sp_populacao.head(3)
| id | ano | sigla_uf | id_municipio | populacao | |
|---|---|---|---|---|---|
| 0 | 1 | 1991 | SP | 3500105 | 32092.0 |
| 1 | 2 | 1992 | SP | 3500105 | 32085.0 |
| 2 | 3 | 1993 | SP | 3500105 | 32099.0 |
Como já importamos os dados devidamente, agora podemos fazer as análises e visualizações que desejamos com os dados que temos. Pretendemos fazer gráficos interativos, mas precisamos salva-los, então vamos configurar o chart studio para salvar esses gráficos:
# usuario = getpass.getpass()
# senha = getpass.getpass()
# chart_studio.tools.set_credentials_file(username=usuario, api_key=senha)
# chart_studio.tools.set_config_file(world_readable=True, sharing='public')
Para esta análise, usou-se uma tabela que contém dados que caracterizam brevemente a escola e, principalmente, dados do indicador socioeconômico da escola, o INSE. O indicador varia de 0 a 10, sendo 10 a escola com o nível socioeconômico mais baixo e 0 a escola com nível mais alto. O INSE é calculado a partir dos questionários do SARESP. Ele considera as seguintes variáveis: grau de escolaridade dos pais, posse de bens de consumo duráveis na residência e renda familiar.
Inicialmente, realizou-se a leitura da base com Geopandas e uma filtragem nos dados, para trabalhar somente com o necessário. Posteriormente, ainda, foram unidas as bases de dados com informações espaciais e do INSE do munícipio.
#selecionando as colunas necessárias
sp_nse = sp_nse[['id_municipio','nivel_socio_economico']].copy()
#tranformando em numérico
sp_nse["nivel_socio_economico"] = pd.to_numeric(sp_nse["nivel_socio_economico"]).copy()
#transformando NaN em 0
sp_nse.fillna(0, inplace = True)
#Renomeando coluna id_municipio para posteriormente juntar as tabelas
sp_nse.rename(columns={"id_municipio": "id"}, inplace = True)
#agrupando por id e tirando a média
sp_nse=sp_nse.groupby("id").mean()
#juntando as tabelas com a geometria e os dados por id
sp_nse_f = mapassp.join(sp_nse, on="id", how="left")
sp_nse_f
| id | name | geometry | nivel_socio_economico | |
|---|---|---|---|---|
| 0 | 3500105 | Adamantina | POLYGON ((-51.05787 -21.39888, -51.05365 -21.4... | 4.000000 |
| 1 | 3500204 | Adolfo | POLYGON ((-49.65478 -21.20607, -49.63847 -21.2... | 4.190000 |
| 2 | 3500303 | Aguaí | POLYGON ((-47.20890 -21.97129, -47.20297 -21.9... | 5.119091 |
| 3 | 3500402 | Águas da Prata | POLYGON ((-46.70755 -21.82895, -46.70380 -21.8... | 5.600000 |
| 4 | 3500501 | Águas de Lindóia | POLYGON ((-46.61147 -22.43496, -46.60302 -22.4... | 3.940000 |
| ... | ... | ... | ... | ... |
| 640 | 3557006 | Votorantim | POLYGON ((-47.37268 -23.51783, -47.37047 -23.5... | 4.268462 |
| 641 | 3557105 | Votuporanga | POLYGON ((-50.02073 -20.33347, -50.01275 -20.3... | 4.268889 |
| 642 | 3557154 | Zacarias | POLYGON ((-49.99521 -21.03253, -49.98434 -21.0... | 4.450000 |
| 643 | 3557204 | Chavantes | POLYGON ((-49.68539 -22.94866, -49.68396 -22.9... | 5.020000 |
| 644 | 3557303 | Estiva Gerbi | POLYGON ((-46.91399 -22.19933, -46.91221 -22.2... | 3.860000 |
645 rows × 4 columns
Na primeira plotagem, abaixo, é possível visualizar os nível socioeconômico (média das escolas) por município.
#plotagem do mapa do INSE por munícipio
fig, ax = plt.subplots(figsize=(10, 10))
sp_nse_f.plot(ax=ax, column='nivel_socio_economico', cmap='coolwarm', legend=True,
legend_kwds={'label': "Nível socioeconômico", 'shrink': 0.3} )
plt.title('Nível socioeconômico (média das escolas) por município')
ax.axis('off');
Pelo mapa, é vísivel que alguns munícipios possuem níveis socioeconômicos (média das escolas) mais elevados que outros. O município com melhor índice de nível socioecônomico é águas de São Pedro (1.66), já com o pior INSE, é Itaóca (8.56), como verificado pelos gráficos de barras a seguir, que indicam os 10 melhores e 10 piores INSE.
df = sp_nse_f[['name','nivel_socio_economico']] #tabela com os dados necessários para fazer os gráficos de barra
df1=df.sort_values('nivel_socio_economico', ascending=False).reset_index().head(10) #ordenação decrescente dos dados
df2=df.sort_values('nivel_socio_economico').reset_index().head(10)#ordenação crsescente dos dados
#plotagem do gráfico de barra dos 10 piores INSE com Plotly
fig = px.bar(df1,
x='name', y="nivel_socio_economico", height=500, width=700,
barmode='stack', labels={"name": "Nome", 'nivel_socio_economico':"Nível socioeconômico "})
fig.update_layout(title = '10 municípios com piores níveis socioeconômicos (média das escolas)')
fig.update_xaxes(title = 'Município')
fig.update_yaxes(title = 'Nível socioeconômico médio das escolas')
fig.show()
#plotagem do gráfico de barra dos 10 melhores INSE com Plotly
fig = px.bar(df2,
x='name', y="nivel_socio_economico", height=500, width=700,
barmode='stack', labels={"name": "Nome", 'nivel_socio_economico':"Nível socioeconômico "})
fig.update_layout(title = '10 municípios com melhores nível socioeconômico (média das escolas)')
fig.update_xaxes(title = 'Município')
fig.update_yaxes(title = 'Nível socioeconômico médio das escolas')
fig.show()
A tabela usada, nesse caso, contém dados da nota do Índice de Desenvolvimento da Educação do Estado de São Paulo (IDESP) para cada escola da rede estadual do estado de São Paulo, estabelecendo metas que as escolas devem alcançar ano a ano e seus resultados. Disponibilizada pela Secretaria de Educação do Estado de São Paulo, a tabela identifica cada escola através de códigos nacionais e estaduais e disponibiliza variáveis que demonstram para aquela dada escola, seu aproveitamento no IDESP. O IDESP é um indicador que avalia a qualidade da escola, sendo composto por dois critérios: o desempenho dos alunos nos exames de proficiência do SARESP (o quanto aprenderam) e o fluxo escolar (em quanto tempo aprenderam).Assim, nesta avaliação, considera–se que uma boa escola é aquela em que a maior parte dos alunos aprende as competências e habilidades requeridas para a sua série/ano, num período de tempo ideal – o ano letivo.
Para a realização das análises, novamente realizou-se uma limpeza nos dados e selecionou-se o ano de 2018.
#selecionando as colunas necessárias
sp_idesp = sp_idesp[["ano",'id_municipio','nota_idesp_ef_iniciais', "nota_idesp_ef_finais", "nota_idesp_em"]]
#tranformando em numéricos
sp_idesp['nota_idesp_ef_iniciais'] = pd.to_numeric(sp_idesp["nota_idesp_ef_iniciais"])
sp_idesp['nota_idesp_ef_finais'] = pd.to_numeric(sp_idesp["nota_idesp_ef_finais"])
sp_idesp['nota_idesp_em'] = pd.to_numeric(sp_idesp["nota_idesp_em"])
#mudando nome da coluna
sp_idesp.rename(columns={"id_municipio": "id"}, inplace = 1)
#selecionando os dados do ano de 2018
sp_idesp_2018_na = sp_idesp[sp_idesp["ano"] == 2018]
#agrupando por id e tirando a média
sp_idesp_2018_na=sp_idesp_2018_na.groupby("id").mean()
#juntando as tabelas dos dados e da geometria
sp_idesp_2018 = mapassp.join(sp_idesp_2018_na, on="id", how="left")
#tranformando NaN em 0
sp_idesp_2018.fillna(0, inplace = True)
sp_idesp_2018
| id | name | geometry | ano | nota_idesp_ef_iniciais | nota_idesp_ef_finais | nota_idesp_em | |
|---|---|---|---|---|---|---|---|
| 0 | 3500105 | Adamantina | POLYGON ((-51.05787 -21.39888, -51.05365 -21.4... | 2018.0 | 0.00 | 3.423333 | 2.250000 |
| 1 | 3500204 | Adolfo | POLYGON ((-49.65478 -21.20607, -49.63847 -21.2... | 2018.0 | 0.00 | 0.000000 | 3.170000 |
| 2 | 3500303 | Aguaí | POLYGON ((-47.20890 -21.97129, -47.20297 -21.9... | 2018.0 | 6.70 | 2.943333 | 2.436667 |
| 3 | 3500402 | Águas da Prata | POLYGON ((-46.70755 -21.82895, -46.70380 -21.8... | 2018.0 | 0.00 | 4.250000 | 3.110000 |
| 4 | 3500501 | Águas de Lindóia | POLYGON ((-46.61147 -22.43496, -46.60302 -22.4... | 2018.0 | 6.97 | 3.650000 | 3.630000 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 640 | 3557006 | Votorantim | POLYGON ((-47.37268 -23.51783, -47.37047 -23.5... | 2018.0 | 0.00 | 3.728462 | 2.754615 |
| 641 | 3557105 | Votuporanga | POLYGON ((-50.02073 -20.33347, -50.01275 -20.3... | 2018.0 | 0.00 | 3.665556 | 2.413750 |
| 642 | 3557154 | Zacarias | POLYGON ((-49.99521 -21.03253, -49.98434 -21.0... | 2018.0 | 0.00 | 2.800000 | 2.650000 |
| 643 | 3557204 | Chavantes | POLYGON ((-49.68539 -22.94866, -49.68396 -22.9... | 2018.0 | 0.00 | 2.620000 | 2.560000 |
| 644 | 3557303 | Estiva Gerbi | POLYGON ((-46.91399 -22.19933, -46.91221 -22.2... | 2018.0 | 0.00 | 0.000000 | 3.560000 |
645 rows × 7 columns
No mapas a seguir, pode-se visualizar, inicialmente, que as escolas estaduais no estado de São Paulo, em geral, abordam o Ensino Médio, já as séries iniciais possuem muitos valores vazios por município, ou seja, não possuem avaliação do IDESP pois, provavelmente, são escolas municipais que fornecem esse nível de educação.
Ainda, até 2030, a meta é que as escolas alcancem índice 7 para o ciclo que vai do 1° ao 5° ano, índice 6 para o ciclo que vai do 6° ao 9° ano e índice 5 para o Ensino Médio. Pelas plotagens, a séries iniciais parecem estar mais próximas do objetivo, mesmo que tenham que alcançar notas mais altas.
#plotagem de gráficos do IDESP por nível de ensino
fig, axs = plt.subplots(3, figsize=(10, 12))
sp_idesp_2018.plot(ax=axs[0], column='nota_idesp_em', cmap='Oranges', legend=True, legend_kwds={'label': "Nota média IDESP", 'shrink': 0.5})
axs[0].set_title('Notas do IDESP no Ensino Médio (média das escolas) por município')
sp_idesp_2018.plot(ax=axs[1], column='nota_idesp_ef_finais', cmap='Oranges', legend=True, legend_kwds={'label': "Nota média IDESP", 'shrink': 0.5})
axs[1].set_title('Notas do IDESP no Ensino Fundamental - série finais - (média das escolas) por município')
sp_idesp_2018.plot(ax=axs[2], column='nota_idesp_ef_iniciais', cmap='Oranges', legend=True, legend_kwds={'label': "Nota média IDESP", 'shrink': 0.5})
axs[2].set_title('Notas do IDESP no Ensino Fundamental - série iniciais - (média das escolas) por município')
plt.tight_layout();
#análises dos IDESP médios do estado por nível
media_efi=sp_idesp_2018_na['nota_idesp_ef_iniciais'].dropna().mean()
media_eff=sp_idesp_2018_na['nota_idesp_ef_finais'].dropna().mean()
media_em=sp_idesp_2018_na['nota_idesp_em'].dropna().mean()
print ("Média IDESP para as séries iniciais:",media_efi)
print ("Média IDESP para as séries finais:",media_eff)
print ("Média IDESP para o Ensino Médio:",media_em)
Média IDESP para as séries iniciais: 5.816202782521359 Média IDESP para as séries finais: 3.5060397587489422 Média IDESP para o Ensino Médio: 2.7797229578798786
Com as médias calculadas, observa-se que, realmente, as séries iniciais, pela média, estão mais próximas de alcançar o objetivo. Contudo, existem munícipios que já alcançaram-no, como verificado pelas tabelas abaixo.
#analisando municípios que já atingiram a meta do IDESP para 2030
obj_efi=sp_idesp_2018[sp_idesp_2018["nota_idesp_ef_iniciais"] >= 7]
print("Munícipio que já atingiram a meta para séries iniciais (analisando a média):")
obj_efi[['name', "nota_idesp_ef_iniciais"]].sort_values('nota_idesp_ef_iniciais', ascending=False)
Munícipio que já atingiram a meta para séries iniciais (analisando a média):
| name | nota_idesp_ef_iniciais | |
|---|---|---|
| 496 | Rosana | 8.035 |
| 191 | Gastão Vidigal | 7.800 |
| 420 | Penápolis | 7.640 |
| 527 | Santana da Ponte Pensa | 7.560 |
| 260 | Itápolis | 7.430 |
| 339 | Mirante do Paranapanema | 7.170 |
| 177 | Fernandópolis | 7.090 |
| 219 | Iacri | 7.030 |
| 253 | Itaóca | 7.010 |
obj_efi=sp_idesp_2018[sp_idesp_2018["nota_idesp_ef_finais"] >= 6]
print("Munícipio que já atingiram a meta para séries finais (analisando a média):")
obj_efi[['name', "nota_idesp_ef_finais"]].sort_values('nota_idesp_ef_finais', ascending=False)
Munícipio que já atingiram a meta para séries finais (analisando a média):
| name | nota_idesp_ef_finais | |
|---|---|---|
| 511 | Santa Adélia | 6.72 |
| 316 | Macaubal | 6.59 |
| 527 | Santana da Ponte Pensa | 6.53 |
| 638 | Vista Alegre do Alto | 6.51 |
| 435 | Pirajuí | 6.00 |
obj_efi=sp_idesp_2018[sp_idesp_2018["nota_idesp_em"] >= 5]
print("Munícipio que já atingiram a meta para o Ensino Médio (analisando a média):")
obj_efi[['name', "nota_idesp_em"]].sort_values('nota_idesp_em', ascending=False)
Munícipio que já atingiram a meta para o Ensino Médio (analisando a média):
| name | nota_idesp_em | |
|---|---|---|
| 511 | Santa Adélia | 6.00 |
| 316 | Macaubal | 5.46 |
| 572 | Sebastianópolis do Sul | 5.42 |
| 188 | Gabriel Monteiro | 5.00 |
Nesse caso, usou-se uma base de dados com informações mensais das ocorrências registradas pela Secretaria de Segurança Publica de São Paulo (SSP). Fez-se a limpeza de dados e acrescentamos uma coluna de ocorrências totais com os dados que supomos relevantes.
#removendo coluna
df_seguranca.drop('id', axis='columns', inplace=True)
#renomeando coluna
df_seguranca.rename(columns={"id_municipio": "id"}, inplace = 1)
#selecionando dados do ano de 2018
df_seguranca_2018 = df_seguranca[df_seguranca["ano"] == 2018]
#agrupando por id e somando os valores
df_seguranca_2018 = df_seguranca_2018.groupby("id").sum()
#juntando as tabelas
df_seguranca_2018= mapassp.join(df_seguranca_2018,on="id", how="left")
#somando colunas com dados relevantes sobre ocorrências de criminalidade
df_seguranca_2018["total"]=df_seguranca_2018["homicidio_doloso"]+df_seguranca_2018["homicidio_culposo_por_acidente_de_transito"]+df_seguranca_2018["homicidio_culposo_outros"]+df_seguranca_2018["tentativa_de_homicidio"]+df_seguranca_2018["lesao_corporal_seguida_de_morte"]+df_seguranca_2018["lesao_corporal_dolosa"]+df_seguranca_2018["lesao_corporal_culposa_outras"]+df_seguranca_2018["homicidio_doloso"]+df_seguranca_2018["latrocinio"]+df_seguranca_2018["total_de_estupro"]+df_seguranca_2018["roubo_outros"]+df_seguranca_2018["roubo_de_veiculo"]+df_seguranca_2018["roubo_a_banco"]+df_seguranca_2018["roubo_de_carga"]+df_seguranca_2018["roubo_de_carga"]+df_seguranca_2018["furto_outros"]+df_seguranca_2018["furto_de_veiculo"]
#plotagem do mapa sobre o total de ocorrências
fig, ax = plt.subplots(figsize=(10, 10))
df_seguranca_2018.plot(ax=ax, column='total', cmap='coolwarm', legend=True, legend_kwds={'shrink': 0.3})
plt.title('Ocorrências de crimes no ano de 2018')
ax.axis('off');
Na plotagem do mapa do estado com as ocorrências totais, é evidente o quanto São Paulo se destaca com um número de casos bastante elevado em relação aos outros municípios. Já Torre de Pedra é o município com o menor número de ocorrência, apenas 6.
#munícipio com menores números de ocorrências
menores_ocor=df_seguranca_2018.sort_values('total').reset_index().head(10)
menores_ocor[["name", "total"]]
| name | total | |
|---|---|---|
| 0 | Torre de Pedra | 6.0 |
| 1 | Dirce Reis | 7.0 |
| 2 | Aspásia | 9.0 |
| 3 | Santa Salete | 10.0 |
| 4 | Arco-Íris | 11.0 |
| 5 | Marinópolis | 12.0 |
| 6 | Vitória Brasil | 13.0 |
| 7 | Trabiju | 14.0 |
| 8 | Paulistânia | 14.0 |
| 9 | Borá | 15.0 |
Contudo, essa soma total de ocorrências pode ser muito influenciada pela quantidade de população do local. Portanto, vale a pena analisar os dados com as proporções. Para isso, fez-se uso de uma base de dados com a quantidade de população por município.
#selecionando os dados sobre a população no ano de 2018
sp_populacao_2018 =sp_populacao[sp_populacao["ano"]==2018]
sp_populacao_2018=sp_populacao_2018[["id_municipio", "populacao"]]
#renomeando coluna
sp_populacao_2018.rename(columns={"id_municipio": "id"}, inplace = 1)
#unindo bases de dados
df_seg_pop=pd.merge(sp_populacao_2018, df_seguranca_2018, how = 'inner', on = 'id')
#criando coluna com as proporções
df_seg_pop["proporcao"]=df_seg_pop["total"]/df_seg_pop["populacao"]
from geopandas import GeoDataFrame
#plotagem do mapa sobre o total de ocorrências
df_seg_pop = GeoDataFrame(df_seg_pop)
fig, ax = plt.subplots(figsize=(10, 10))
df_seg_pop.plot(ax=ax, column='proporcao', cmap='coolwarm', legend=True, legend_kwds={'shrink': 0.3})
plt.title('Proporção entre ocorrências e população no ano de 2018')
ax.axis('off');
#munícipios com maiores proporções números de ocorrências por população
menores_ocor=df_seg_pop.sort_values('proporcao', ascending=False).reset_index().head(10)
menores_ocor[["name", "proporcao"]]
| name | proporcao | |
|---|---|---|
| 0 | Ilha Comprida | 0.062927 |
| 1 | Mongaguá | 0.054099 |
| 2 | Jeriquara | 0.043876 |
| 3 | Itanhaém | 0.041962 |
| 4 | São Paulo | 0.036517 |
| 5 | Peruíbe | 0.036300 |
| 6 | Diadema | 0.033426 |
| 7 | Praia Grande | 0.032982 |
| 8 | Santo André | 0.032045 |
| 9 | Miracatu | 0.030574 |
Com as novas visualizações, fica notável o quanto ocorreram mudanças trabalhando com proporções. Neste caso, Torre de Pedra continua sendo o munícipio com menor registro de ocorrências. Contudo, Ilha Comprida, Mongaguá, Jeriquara e Itanhaém possuem proporções mais elevadas do que São Paulo, que se destacava no mapa anterior por disparar com o número de ocorrências em relação aos outros munícipios.
Essa analíse visa a explorar as diferenças no nível de formação escolar dos municipios de São Paulo. As tabelas contém dados do fluxo escolar agregado a nível de ensino de cada escola. Para quantificar o fluxo, utiliza-se 3 variáveis para cada nível de ensino: a proporção de alunos aprovados, a proporção de alunos reprovados e a proporção de alunos que abandonaram o ano escolar.
fluxo_escolar = fluxo_escolar[['id_municipio','prop_aprovados_em', 'prop_reprovados_em', 'prop_abandono_em']]
fluxo_escolar["prop_aprovados_em"] = pd.to_numeric(fluxo_escolar["prop_aprovados_em"])
fluxo_escolar["prop_reprovados_em"] = pd.to_numeric(fluxo_escolar["prop_reprovados_em"])
fluxo_escolar["prop_abandono_em"] = pd.to_numeric(fluxo_escolar["prop_abandono_em"])
fluxo_escolar.fillna(0, inplace = True)
fluxo_escolar.rename(columns={"id_municipio": "id"}, inplace = 1)
fluxo_escolar=fluxo_escolar.groupby("id").mean()
fluxo_escolar = mapassp.join(fluxo_escolar, on="id", how="left")
fluxo_escolar.head(15)
| id | name | geometry | prop_aprovados_em | prop_reprovados_em | prop_abandono_em | |
|---|---|---|---|---|---|---|
| 0 | 3500105 | Adamantina | POLYGON ((-51.05787 -21.39888, -51.05365 -21.4... | 77.656136 | 9.828708 | 1.404415 |
| 1 | 3500204 | Adolfo | POLYGON ((-49.65478 -21.20607, -49.63847 -21.2... | 81.698453 | 3.012857 | 1.002975 |
| 2 | 3500303 | Aguaí | POLYGON ((-47.20890 -21.97129, -47.20297 -21.9... | 52.933259 | 6.949182 | 1.228948 |
| 3 | 3500402 | Águas da Prata | POLYGON ((-46.70755 -21.82895, -46.70380 -21.8... | 42.664264 | 1.623514 | 0.156667 |
| 4 | 3500501 | Águas de Lindóia | POLYGON ((-46.61147 -22.43496, -46.60302 -22.4... | 77.279346 | 6.002794 | 4.217860 |
| 5 | 3500550 | Águas de Santa Bárbara | POLYGON ((-49.19750 -22.72188, -49.19029 -22.7... | 76.196319 | 7.510396 | 5.182174 |
| 6 | 3500600 | Águas de São Pedro | POLYGON ((-47.87540 -22.59128, -47.86142 -22.6... | 83.207825 | 2.363604 | 0.142857 |
| 7 | 3500709 | Agudos | POLYGON ((-48.91106 -22.33760, -48.91545 -22.3... | 77.315027 | 7.045012 | 4.528850 |
| 8 | 3500758 | Alambari | POLYGON ((-47.80675 -23.48821, -47.79898 -23.4... | 79.788360 | 6.859364 | 2.241165 |
| 9 | 3500808 | Alfredo Marcondes | POLYGON ((-51.32568 -21.87177, -51.33163 -21.8... | 75.207949 | 8.914508 | 4.767544 |
| 10 | 3500907 | Altair | POLYGON ((-49.08594 -20.43743, -49.08830 -20.4... | 83.404201 | 2.219665 | 3.265022 |
| 11 | 3501004 | Altinópolis | POLYGON ((-47.41166 -20.80867, -47.40621 -20.8... | 76.131045 | 6.009765 | 6.748079 |
| 12 | 3501103 | Alto Alegre | POLYGON ((-50.14669 -21.52728, -50.14428 -21.5... | 81.676869 | 4.079180 | 3.132840 |
| 13 | 3501152 | Alumínio | POLYGON ((-47.31383 -23.46525, -47.30995 -23.4... | 81.713879 | 3.941049 | 0.059359 |
| 14 | 3501202 | Álvares Florence | POLYGON ((-49.95703 -20.17169, -49.95412 -20.1... | 81.088438 | 3.589337 | 4.210002 |
#Plotando a figura
fig = px.box(fluxo_escolar, y="prop_aprovados_em", points = "all", hover_data=['name'], labels={'y':'gdp'})
fig.update_layout(title_text="Proporção de concluintes do ensino médio no estado de SP")
fig.show()
Analisando os dados através do novo gráfico podemos ver que o nível de aprovação do ensino médio no estado de São Paulo não é tão baixo e tem uma média de aprovação no ensino médio entre os municípios superior a 77 %.
# Plotando a figura
fig, ax = plt.subplots(figsize=(10, 10))
fluxo_escolar.plot( ax=ax, column='prop_aprovados_em', cmap='Blues', legend=True, legend_kwds={'shrink': 0.4})
ax.axis('off');
plt.title('Proporção de aprovados no ensino médio por munícipio no estado de SP', size=15);
Podemos observar graças a esse gráfico que a maioria dos estados tem uma aprovação de alunos superior a 50 %, entretanto o que acontece com os alunos que não são aprovados ?
# Plotando a figura
fig, axs = plt.subplots(2, figsize=(14, 14))
fluxo_escolar.plot(ax=axs[0], column='prop_reprovados_em', cmap='Oranges', legend=True, legend_kwds={'label': "Porcentagem de reprovados", 'shrink': 0.6})
axs[0].set_title('Mapa do estado de SP com a proporção de reprovados no ensino médio', fontsize=15)
fluxo_escolar.plot(ax=axs[1], column='prop_abandono_em', cmap='Oranges', legend=True, legend_kwds={'label': "Porcentagem de abandonos", 'shrink': 0.6})
axs[1].set_title('Mapa do estado de SP com a proporção de abandonos no ensino médio', fontsize=15)
;
''
fluxo_escolar['prop_aprovados_em'].describe()
# 69.735006 é o primeiro quartil da prop_aprovados
flux_menor = fluxo_escolar[fluxo_escolar['prop_aprovados_em'] < 69.735006]
# Imprimindo a tabela comparativa
tab = flux_menor[['name', "prop_reprovados_em", "prop_abandono_em"]]
tab
| name | prop_reprovados_em | prop_abandono_em | |
|---|---|---|---|
| 2 | Aguaí | 6.949182 | 1.228948 |
| 3 | Águas da Prata | 1.623514 | 0.156667 |
| 15 | Álvares Machado | 12.203966 | 5.567830 |
| 18 | Americana | 4.560133 | 2.109248 |
| 21 | Amparo | 3.523008 | 3.350330 |
| ... | ... | ... | ... |
| 625 | Urânia | 1.736886 | 0.090037 |
| 630 | Valparaíso | 1.746424 | 4.329494 |
| 631 | Vargem | 10.590512 | 5.442857 |
| 632 | Vargem Grande do Sul | 6.850661 | 4.650581 |
| 635 | Vera Cruz | 1.948412 | 0.096667 |
171 rows × 3 columns
Pela tabela e os dois gráficos acima podemos supor que entre os alunos do ensino médio houve mais alunos que foram reprovados do que alunos que abadoram a escola. Entretanto nem o gráfico e nem a tabela nos permitem de confirrmarr esta hipótese.
# "Categorizando" a comparação entre a probabilidade de ser reprovado e abandonar.
mais_reprovados = (tab['prop_reprovados_em'] > tab['prop_abandono_em'])
print(f"Número de escolas com mais reprovações do que abandonos: {mais_reprovados.sum()}")
print(f"Número de escolas com mais abandonos do que reprovações: {len(mais_reprovados) - mais_reprovados.sum()}")
Número de escolas com mais reprovações do que abandonos: 159 Número de escolas com mais abandonos do que reprovações: 12
Graças a tabela comparativa acima é possível afirmar que na maioria dos municipios com baixa taxa de aprovação, os alunos não completaram o ensino médio devido principalmente a reprovação. Entretanto o que causa essa reprovação?
Agora parte-se para as análises correlacionadas. Será que o Índice de nível socioeconômico das escolas (média) por munícipio está relacionado com as notas do IDESP?
Para respondermos essa pergunta, novamente criou-se uma nova tabela com os dados necessários.
# Unindo tabelas do INSE e do IDESP por id
IDESP_INSE=sp_nse.join(sp_idesp_2018_na, on="id", how="left")
IDESP_INSE
| nivel_socio_economico | ano | nota_idesp_ef_iniciais | nota_idesp_ef_finais | nota_idesp_em | |
|---|---|---|---|---|---|
| id | |||||
| 3500105 | 4.000000 | 2018.0 | NaN | 3.423333 | 2.250000 |
| 3500204 | 4.190000 | 2018.0 | NaN | NaN | 3.170000 |
| 3500303 | 5.119091 | 2018.0 | 6.70 | 2.943333 | 2.436667 |
| 3500402 | 5.600000 | 2018.0 | NaN | 4.250000 | 3.110000 |
| 3500501 | 3.940000 | 2018.0 | 6.97 | 3.650000 | 3.630000 |
| ... | ... | ... | ... | ... | ... |
| 3557006 | 4.268462 | 2018.0 | NaN | 3.728462 | 2.754615 |
| 3557105 | 4.268889 | 2018.0 | NaN | 3.665556 | 2.413750 |
| 3557154 | 4.450000 | 2018.0 | NaN | 2.800000 | 2.650000 |
| 3557204 | 5.020000 | 2018.0 | NaN | 2.620000 | 2.560000 |
| 3557303 | 3.860000 | 2018.0 | NaN | NaN | 3.560000 |
644 rows × 5 columns
Ao plotar um gráfico de dispersão, esperava que os dados seguissem uma tendência de quanto menor fossem os INSE (já que quanto mais próximo de 0 melhor), maior seriam as notas do IDESP. Contudo, não é o que acontece.
#plotando gráfico de dispersão relacionando IDESP e INSE
fig=px.scatter(IDESP_INSE, x = "nivel_socio_economico", y = "nota_idesp_em", labels={"nivel_socio_economico": "INSE", "nota_idesp_em":"IDESP" })
fig.update_traces(marker=dict(size = 8,line = dict(width = 2)),selector=dict(mode = 'markers'))
fig.update_layout(title = 'IDESP X INSE')
fig.update_xaxes(title = 'índice de nível socioeconômico')
fig.update_yaxes(title = 'Notas IDESP - Ensino Médio')
fig.show()
# Calculando a correlação dos dados
Correlação= IDESP_INSE['nivel_socio_economico'].corr(IDESP_INSE['nota_idesp_em'])
print(f"Correlação dos dados:", Correlação)
Correlação dos dados: -0.11233666198194993
Ao analisar a correlação, ela é negativa (que até concordaria com a ideia de quanto melhor o INSE maior o IDESP), mas muita baixa (cerca de -0,11), portanto, não pode-se concluir que os dados possuem relação.
Já Índice de nível socioeconômico das escolas (média) por munícipio está relacionado com a Segurança Pública de São Paulo?
INSE_SSP=pd.merge(sp_nse, df_seg_pop, how = 'inner', on = 'id')
# Plotando gráfico de dispersão relacionando IDESP e INSE
fig=px.scatter(INSE_SSP, x = "nivel_socio_economico", y = "proporcao", labels={"nivel_socio_economico": "INSE", "proporcao":"Proporção entre ocorrências e população" })
fig.update_traces(marker=dict(size = 8,line = dict(width = 2)),selector=dict(mode = 'markers'))
fig.update_layout(title = 'Segurança no Estado de São Paulo X INSE')
fig.update_xaxes(title = 'Índice de nível socioeconômico')
fig.update_yaxes(title = 'Segurança no Estado de São Paulo')
fig.show()
# Calculando a correlação dos dados
Correlação1= INSE_SSP['nivel_socio_economico'].corr(INSE_SSP['proporcao'])
print(f"Correlação dos dados:", Correlação1)
Correlação dos dados: -0.16829946816916258
Novamente o que temos é um resultado inesperado, afinal, em geral, pensa-se que quanto maior a quantidade de ocorrências, pior seria o INSE, contudo, mesmo havendo uma correlação negativa, ela novamente é muito baixa para afirmar uma relação entre os dados.
Uma das possíveis hipóteses que podemos formular é que o nível de reprovamento e abandono são correlacionados com o total ou com a proporção de crimes nos municípios do estado.
# Coletando os dados
df_seguranca=df_seguranca.groupby("id").mean()
# Excluindo dados desnecessários
flux2 = fluxo_escolar[fluxo_escolar.columns.difference(['geometry', 'description', 'name'])]
df_seguranca = df_seguranca[df_seguranca.columns.difference(['ano', 'mes'])]
# Transformando tipos de dados
sp_populacao.drop("id", axis="columns", inplace=True)
sp_populacao.rename(columns={"id_municipio": "id"}, inplace = 1)
sp_populacao["populacao"] = pd.to_numeric(sp_populacao["populacao"])
# Juntando as duas bases
df_seg_pop = pd.merge(sp_populacao, df_seguranca, how = 'inner', on = 'id')
df_seg_pop = df_seg_pop[df_seg_pop.columns.difference(['ano', 'sigla_uf', 'geometry'])]
df_seg_pop=df_seg_pop.groupby("id").mean()
# Selecionando dados específicos
df_seg_pop = df_seg_pop[['populacao','estupro', 'furto_outros', 'homicidio_doloso', 'latrocinio','tentativa_de_homicidio','lesao_corporal_seguida_de_morte','lesao_corporal_dolosa','total_de_roubo_outros', 'roubo_de_veiculo']]
df2 = flux2.join(df_seg_pop, on="id", how="left")
# Normalizando os dados
abs_scaler = MaxAbsScaler()
abs_scaler.fit(df2)
abs_scaler.max_abs_
scaled_data = abs_scaler.transform(df2)
df_scaled = pd.DataFrame(scaled_data, columns=df2.columns)
df_scaled = df_scaled[df_scaled.columns.difference(['id'])]
df_scaled
| estupro | furto_outros | homicidio_doloso | latrocinio | lesao_corporal_dolosa | lesao_corporal_seguida_de_morte | populacao | prop_abandono_em | prop_aprovados_em | prop_reprovados_em | roubo_de_veiculo | tentativa_de_homicidio | total_de_roubo_outros | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0.003117 | 0.001480 | 0.000539 | 0.001029 | 0.006603 | 0.000000 | 0.003092 | 0.141193 | 0.878739 | 0.526942 | 0.000013 | 0.001915 | 0.000056 |
| 1 | 0.000283 | 0.000222 | 0.000095 | 0.000000 | 0.000521 | 0.000000 | 0.000325 | 0.100834 | 0.924481 | 0.161527 | 0.000004 | 0.000361 | 0.000009 |
| 2 | 0.002550 | 0.002290 | 0.001774 | 0.004117 | 0.005089 | 0.000000 | 0.002814 | 0.123552 | 0.598981 | 0.372563 | 0.000424 | 0.004336 | 0.000379 |
| 3 | 0.001133 | 0.000241 | 0.000285 | 0.000000 | 0.000863 | 0.000000 | 0.000686 | 0.015750 | 0.482779 | 0.087041 | 0.000025 | 0.000108 | 0.000018 |
| 4 | 0.002834 | 0.001037 | 0.000760 | 0.001029 | 0.002282 | 0.000000 | 0.001502 | 0.424043 | 0.874476 | 0.321825 | 0.000165 | 0.001337 | 0.000194 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 640 | 0.012468 | 0.005090 | 0.007510 | 0.001544 | 0.011059 | 0.034091 | 0.009468 | 0.395383 | 0.901475 | 0.251456 | 0.000827 | 0.012828 | 0.001541 |
| 641 | 0.007651 | 0.007221 | 0.003137 | 0.001544 | 0.016711 | 0.000000 | 0.007395 | 0.358008 | 0.800251 | 0.386038 | 0.000113 | 0.007010 | 0.000557 |
| 642 | 0.000000 | 0.000171 | 0.000127 | 0.000000 | 0.000356 | 0.000000 | 0.000201 | 0.168818 | 0.944469 | 0.200831 | 0.000006 | 0.000181 | 0.000008 |
| 643 | 0.002550 | 0.000797 | 0.000634 | 0.001544 | 0.002396 | 0.011364 | 0.001135 | 0.759115 | 0.870202 | 0.237849 | 0.000015 | 0.001301 | 0.000042 |
| 644 | 0.000850 | 0.000716 | 0.000317 | 0.000515 | 0.002067 | 0.000000 | 0.000878 | 0.351134 | 0.904552 | 0.122470 | 0.000064 | 0.001048 | 0.000084 |
645 rows × 13 columns
# Plotando gráfico
corr = df_scaled.corr('spearman')
f,ax = plt.subplots(figsize=(10, 10))
sns.heatmap(corr, cbar = True, square = True, annot = True,
xticklabels= True, yticklabels= True, linewidths=.5, ax=ax)
plt.title('Correlação entre total de ocorrências \n criminosas e fluxo escolar em SP', size=19);
# Calculando proporção de crimes
df_seg_pop2 = df_seg_pop.diff(axis = 1)
# Juntando as duas bases
df3 = flux2.join(df_seg_pop2, on="id", how="left")
df3 = df3[df3.columns.difference(['populacao'])]
# Plotando gráfico
corr = df3.corr('spearman')
f,ax = plt.subplots(figsize=(10, 10))
sns.heatmap(corr, cbar = True, square = True, annot = True,
xticklabels= True, yticklabels= True, linewidths=.5, ax=ax)
plt.title('Correlação entre proporção de ocorrências\n criminosas e fluxo escolar em SP ', size=19);
Ao plotar os dois gráficos é possível descobrir uma certa dependencia. O primeiro gráfico indica que a proporção de aprovação no ensino médio e as ocorências criminosas nos Municípios de SP tem uma correlação negativa abaixo de -0.31 para cada crime, isso evidência que há uma tendência de municípios com um total de crime menor a terem um número de aprovação maior.